﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using GlxRRSDB;
using GlxRRSDB.ReportDataSetTableAdapters;
using GlxRRSDB.Data;
using System.Data.SqlClient;
using System.Data;
using GlxRRSDB.Utils;

namespace GlxRRSDB.Facades
{
    public class MonthlyReportFacade
    {
        #region MEMBER
        private static SqlConnection _connection = null;
        #endregion
        private static SqlConnection Connection
        {
            get
            {
                if (_connection == null)
                {
                    _connection = new SqlConnection();
                    _connection.ConnectionString = GlxRRSDB.Properties.Settings.Default.ReportDBConnectionString;
                }
                return _connection;
            }
        }

        #region SQL COMMAND
        private const string SQL_BO_MONTHLY = "SELECT MONTH(date), SUM(bo) " +
                                                "FROM tblBORevenue " +
                                                "WHERE YEAR(date) =  @year AND CinemaID=@cinemaid " +
                                                "GROUP BY month(date)";
        private const string SQL_CO_MONTHLY = "SELECT MONTH(date), SUM(co) " +
                                                "FROM tblCORevenue " +
                                                "WHERE YEAR(date) =  @year AND CinemaID=@cinemaid " +
                                                "GROUP BY month(date)";
        private const string SQL_ADMISSION_MONTHLY = "SELECT MONTH(date), SUM(admission) " +
                                                "FROM tblBORevenue " +
                                                "WHERE YEAR(date) =  @year AND CinemaID=@cinemaid " +
                                                "GROUP BY month(date)";
        private const string SQL_TARGET_MONTHLY = "SELECT MONTH(date), SUM(AdmitTarget), SUM(), SUM() " +
                                                "FROM tblFilmRevenue " +
                                                "WHERE YEAR(date) =  @year AND CinemaID=@cinemaid " +
                                                "GROUP BY month(date)";
        #endregion

        public static Dictionary<int, Dictionary<int, MonthlyReportData>> getBOMonthlyReport(int year, int cinemaId)
        {
            Dictionary<int, Dictionary<int, MonthlyReportData>> result = new Dictionary<int, Dictionary<int, MonthlyReportData>>();
            Dictionary<int, MonthlyReportData> currentYear = doGetBOMonthlyReport(cinemaId, year);
            Dictionary<int, MonthlyReportData> previousYear = doGetBOMonthlyReport(cinemaId, year - 1);
            Dictionary<int, MonthlyReportData> target = new Dictionary<int, MonthlyReportData>();
            //
            result.Add(year, currentYear);
            result.Add(year - 1, previousYear);
            result.Add(0, target);
            //
            return result;
        }

        public static Dictionary<int, Dictionary<int, MonthlyReportData>> getCOMonthlyReport(int year,int cinemaId)
        {
            Dictionary<int, Dictionary<int, MonthlyReportData>> result = new Dictionary<int, Dictionary<int, MonthlyReportData>>();
            Dictionary<int, MonthlyReportData> currentYear = doGetCOMonthlyReport(cinemaId, year);
            Dictionary<int, MonthlyReportData> previousYear = doGetCOMonthlyReport(cinemaId, year - 1);
            Dictionary<int, MonthlyReportData> target = new Dictionary<int, MonthlyReportData>();
            //
            result.Add(year, currentYear);
            result.Add(year - 1, previousYear);
            result.Add(0, target);
            //
            return result;
        }

        public static Dictionary<int, Dictionary<int, MonthlyReportData>> getAdmissionMonthlyReport(int year, int cinemaId)
        {
            Dictionary<int, Dictionary<int, MonthlyReportData>> result = new Dictionary<int, Dictionary<int, MonthlyReportData>>();
            Dictionary<int, MonthlyReportData> currentYear = doGetAdmissionMonthlyReport(cinemaId, year);
            Dictionary<int, MonthlyReportData> previousYear = doGetAdmissionMonthlyReport(cinemaId, year - 1);
            Dictionary<int, MonthlyReportData> target = new Dictionary<int, MonthlyReportData>();
            //
            result.Add(year, currentYear);
            result.Add(year - 1, previousYear);
            result.Add(0, target);
            //
            return result;
        }

        private static Dictionary<int, MonthlyReportData> doGetBOMonthlyReport(int cinemaId, int year)
        {
            SqlDataReader reader;
            SqlCommand cmd = new SqlCommand(SQL_BO_MONTHLY);
            cmd.Connection = Connection;
            cmd.CommandType = CommandType.Text;
            cmd.Parameters.Add(new SqlParameter("@year", SqlDbType.Int));
            cmd.Parameters.Add(new SqlParameter("@cinemaid", SqlDbType.Int));
            //
            cmd.Parameters[0].Value = year;
            cmd.Parameters[1].Value = cinemaId;
            if (cmd.Connection.State == ConnectionState.Closed)
            {
                cmd.Connection.Open();
            }
            reader = cmd.ExecuteReader();
            Dictionary<int, MonthlyReportData> dict = new Dictionary<int, MonthlyReportData>();
            if (reader.HasRows)
            {
                while (reader.Read())
                {
                    int month = reader.GetInt32(0);
                    long val = reader.GetInt64(1);
                    //
                    MonthlyReportData data = new MonthlyReportData();
                    data.year = year;
                    data.month = month;
                    data.value = val;
                    dict.Add(data.month, data);
                }
            }
            reader.Close();

            return dict;
        }

        private static Dictionary<int, MonthlyReportData> doGetCOMonthlyReport(int cinemaId, int year)
        {
            SqlDataReader reader;
            SqlCommand cmd = new SqlCommand(SQL_CO_MONTHLY);
            cmd.Connection = Connection;
            cmd.CommandType = CommandType.Text;
            cmd.Parameters.Add(new SqlParameter("@year", SqlDbType.Int));
            cmd.Parameters.Add(new SqlParameter("@cinemaid", SqlDbType.Int));
            //
            cmd.Parameters[0].Value = year;
            cmd.Parameters[1].Value = cinemaId;
            if (cmd.Connection.State == ConnectionState.Closed)
            {
                cmd.Connection.Open();
            }
            reader = cmd.ExecuteReader();
            Dictionary<int, MonthlyReportData> dict = new Dictionary<int, MonthlyReportData>();
            if (reader.HasRows)
            {
                while (reader.Read())
                {
                    int month = reader.GetInt32(0);
                    long val = reader.GetInt64(1);
                    //
                    MonthlyReportData data = new MonthlyReportData();
                    data.year = year;
                    data.month = month;
                    data.value = val;
                    dict.Add(data.month, data);
                }
            }
            reader.Close();

            return dict;
        }

        private static Dictionary<int, MonthlyReportData> doGetAdmissionMonthlyReport(int cinemaId, int year)
        {
            SqlDataReader reader;
            SqlCommand cmd = new SqlCommand(SQL_ADMISSION_MONTHLY);
            cmd.Connection = Connection;
            cmd.CommandType = CommandType.Text;
            cmd.Parameters.Add(new SqlParameter("@year", SqlDbType.Int));
            cmd.Parameters.Add(new SqlParameter("@cinemaid", SqlDbType.Int));
            //
            cmd.Parameters[0].Value = year;
            cmd.Parameters[1].Value = cinemaId;
            if (cmd.Connection.State == ConnectionState.Closed)
            {
                cmd.Connection.Open();
            }
            reader = cmd.ExecuteReader();
            Dictionary<int, MonthlyReportData> dict = new Dictionary<int, MonthlyReportData>();
            if (reader.HasRows)
            {
                while (reader.Read())
                {
                    int month = reader.GetInt32(0);
                    long val = reader.GetInt32(1);
                    //
                    MonthlyReportData data = new MonthlyReportData();
                    data.year = year;
                    data.month = month;
                    data.value = val;
                    dict.Add(data.month, data);
                }
            }
            reader.Close();

            return dict;
        }
    }
}
